import sys, code, os, re, cProfile, traceback
import swmmio
from datetime import datetime
import random
import mssql

__version__ = '0.1'
__doc__ = ''
__resources__ = os.environ.get("_MEIPASS2", os.path.abspath(__file__))

class swmmr_exception(Exception):
    def __init__(self, message):
        Exception.__init__(self, message)

def build(server, admin_login, admin_password):
    mssql.build(server = server, 
                admin_login = admin_login, 
                admin_password = admin_password,
                rm_dbname = 'SWMMR',
                setup_path = os.path.join(__resources__, 'setup.sql'))
    h = mssql.handle(server = server, login = admin_login, password = admin_password, database = 'SWMMR', autocommit = True)
    h.sp('Framework.spSetSchemaVersion', Version = __version__, Notes = __doc__)

class handle(mssql.handle):
    def __init__(self, server, login = None, password = None, autocommit = False):
        mssql.handle.__init__(self, 
                              server = server, 
                              login = login, 
                              password = password, 
                              database = 'SWMMR',
                              autocommit = autocommit)

        version = self.ufn('Framework.ufnGetSchemaVersion')
        if version != __version__:
            raise swmmr_exception('This version of the interface does not support SWMMR version ' + version)
        
    def _setSchemaVersion(self):
        self.sp('Framework.spSetSchemaVersion', Version = __version__, Notes = __doc__)

    def importINP(self, model, inp_path, swmmbuild, version = None, docpath = None, desc = None, tags_csv = None):
        tempdir = self.getTempFolder()
        fname = os.environ.get('USERNAME') + '_' + str(random.randint(0,10000)) + '.xml'
        temp_xml_path = os.path.join(tempdir, fname) 
        inp_path = str(inp_path)
        unsupported_tags = swmmio.inp2xml(inp_path, temp_xml_path)

        if len(unsupported_tags) != 0:
            msg = ' '.join(["Can't import '" + inp_path + "'. This file contains the following unsupported sections: "] + unsupported_tags)
            raise swmmr_exception(msg)

        self.sp('Framework.spModelElements_XMLBulkImport', 
                ModelName = model, 
                VersionName = version,
                INP_xml_path = temp_xml_path,
                SWMMBuild = swmmbuild,
                DocPath = docpath,
                Description = desc,
                TagsCSV = tags_csv)
        self.importINPText(model, inp_path)

    def importINPText(self, model_name, inp_path):
        with open(inp_path, 'r') as f:
            inp_text = f.read()
        self.sp('Framework.spImportINPText', ModelName = model_name, INPText = inp_text)

    def getTempFolder(self, local=False):
        return str(self.ufn('Framework.ufnGetTempFolder', Local = local))

    def getModelVersions(self, model):
        return self.sp('Framework.spGetModelVersions', ModelName = model)

    def getModelVersion(self, model, version_id = None, time_until = None):
        version_data = self.sp('Framework.spGetModelVersion', ModelName = model, VersionID = version_id, TimeUntil = time_until)
        if len(version_data) == 1:
            version_data = version_data[0]
        else:
            version_data = None
        return version_data

    def getTags(self):
        tags = self.sp('Framework.spGetTags')
        if isinstance(tags, str):
            tags = [tags]
        return tags

    def addModelTags(self, tags_csv, model = None, model_id = None):
        self.sp('Framework.spAddModelTags', TagsCSV = tags_csv, ModelName = model, ModelID = model_id)

    def setModelTags(self, tags_csv, model, model_id):
        self.sp('Framework.spSetModelTags', TagsCSV = tags_csv, ModelName = model, ModelID = model_id)

    def getModelTagsCSV(self, model):
        return self.ufn('Framework.ufnGetModelTagsCSV', ModelName = model)

    def getModelTags(self, model):
        return self.sp('Framework.spGetModelTags', ModelName = model)

    def updateModelVersion(self, version_id, name, swmmbuild, docpath, desc):
        self.sp('Framework.spUpdateModelVersion', VersionID = version_id, Name = name, SWMMBuild = swmmbuild, DocPath = docpath, Description = desc)

    def prepCascade(self, model, tags_csv, filter_with_and = 0):
        self.refreshCascadeTables()
        self.sp('Framework.spPopulate_Cascading_ModelElements', ModelName = model, TagsCSV = tags_csv, FilterWithAnd = filter_with_and)

    def refreshCascadeTables(self):
        self.execute('USE TEMPDB')
        self.execute("SELECT OBJECT_ID('#Cascading_ModelElements')")
        cme_exists = self.cursor.fetchone()[0] is not None
        self.execute("SELECT OBJECT_ID('#FieldChanges')")
        fc_exists = self.cursor.fetchone()[0] is not None
        self.execute("USE SWMMR")
        if cme_exists:
            self.execute('DELETE FROM #Cascading_ModelElements')
        else:
            table_def = self.ufn('Framework.ufnGetCMETableDef')
            self.execute(table_def)

        if fc_exists:
            self.execute('DELETE FROM #FieldChanges')
        else:
            table_def = self.ufn('Framework.ufnGetFCTableDef')
            self.execute(table_def)

    def CME_Commit(self, model):
        return self.sp('Framework.spCME_Commit', CascadingModelName = model)

    def getModelNames(self, active_only = False):
        return self.sp('Framework.spGetModelNames', ActiveOnly = active_only)

    def getAllModels(self, tags_csv):
        return self.sp('Framework.spGetAllModels', TagsCSV = tags_csv)

    def CME_GetModels(self):
        return self.sp('Framework.spCME_GetModels')

    def CME_Get(self):
        try:
            cascade_MEs = self.sp('Framework.spCME_Get')
        except:
            raise swmmr_exception("Error: #Cascading_ModelElements hasn't been created yet.")

        return cascade_MEs

    def CME_GetSubset(self, model_name=None, elclasses=None):
        num_poss_elclasses = 6
        if elclasses is not None:
            elclasses = list(elclasses)
            if len(elclasses) == 0:
                elclasses = [None for i in xrange(num_poss_elclasses)]
            else:
                if not isinstance(elclasses, list):
                    elclasses = [elclasses]

            if len(elclasses) > num_poss_elclasses:
                raise swmmr_exception('CME_GetSubset: Too many element classes supplied')
            elif len(elclasses) < num_poss_elclasses:
                elclasses.extend([None for i in xrange(num_poss_elclasses - len(elclasses))])
        else:
            elclasses = [None for i in xrange(num_poss_elclasses)]

        return self.sp('Framework.spCME_GetSubset', 
                        ModelName = model_name,
                        Class01 = elclasses[0],
                        Class02 = elclasses[1],
                        Class03 = elclasses[2],
                        Class04 = elclasses[3],
                        Class05 = elclasses[4],
                        Class06 = elclasses[5])

    def CME_SetSubsetState(self, commit_value, model_name=None, elclasses=None, cme_id=None):
        num_poss_elclasses = 6
        if elclasses is not None:
            if not isinstance(elclasses, list):
                elclasses = [elclasses]
            elclasses = list(elclasses)
            if len(elclasses) == 0:
                elclasses = [None for i in xrange(num_poss_elclasses)]

            if len(elclasses) > num_poss_elclasses:
                raise swmmr_exception('CME_GetSubset: Too many element classes supplied')
            elif len(elclasses) < num_poss_elclasses:
                elclasses.extend([None for i in xrange(num_poss_elclasses - len(elclasses))])
        else:
            elclasses = [None for i in xrange(num_poss_elclasses)]

        return self.sp('Framework.spCME_SetSubsetState', 
                       CommitValue = commit_value, 
                       CME_ID = cme_id, 
                       ModelName = model_name, 
                       Class01 = elclasses[0],
                       Class02 = elclasses[1],
                       Class03 = elclasses[2],
                       Class04 = elclasses[3],
                       Class05 = elclasses[4],
                       Class06 = elclasses[5])

    def CME_GetSubsetState(self, model_name = None, elclasses = None, cme_id = None):
        num_poss_elclasses = 6
        if elclasses is not None:
            if not isinstance(elclasses, list):
                elclasses = [elclasses]
            elclasses = list(elclasses)
            if len(elclasses) == 0:
                elclasses = [None for i in xrange(num_poss_elclasses)]

            if len(elclasses) > num_poss_elclasses:
                raise swmmr_exception('CME_GetSubset: Too many element classes supplied')
            elif len(elclasses) < num_poss_elclasses:
                elclasses.extend([None for i in xrange(num_poss_elclasses - len(elclasses))])
        else:
            elclasses = [None for i in xrange(num_poss_elclasses)]

        return self.sp('Framework.spCME_GetSubsetState', 
                        CME_ID = cme_id,
                        ModelName = model_name, 
                        Class01 = elclasses[0],
                        Class02 = elclasses[1],
                        Class03 = elclasses[2],
                        Class04 = elclasses[3],
                        Class05 = elclasses[4],
                        Class06 = elclasses[5])

    def getSupportedSWMMBuilds(self):
        supported = self.sp('Framework.spGetSupportedSWMMBuilds')
        return [supported] if isinstance(supported, str) else supported

    def getModelElementClasses(self, model_name):
        elclasses = self.sp('Framework.spGetModelElementClasses', ModelName = model_name)
        if not isinstance(elclasses, list):
            elclasses = [elclasses]
        return elclasses

    def exportElementClass(self, elclass, model, active_only = 1, version_id = None, time_until = None):
        return self.sp('Framework.spExportElementClass', 
                        ElementClass = elclass, 
                        ModelName = model, 
                        ActiveOnly = active_only, 
                        VersionID = version_id,
                        TimeUntil = time_until)

    def exportModel(self, model, path, version_id = None, time_until = None, preamble=True):
        elclasses = self.getModelElementClasses(model)
        inp_f = swmmio.INP(path, new=True)
        for elclass in elclasses:
            table = self.exportElementClass(elclass, model, active_only = 1, version_id = version_id, time_until = time_until)
            colnames = self.getColumnNames('SWMM_' + elclass)
            put_method = getattr(inp_f, 'put_' + elclass)
            put_method(table, colnames)

        if preamble:
            version_data = self.getModelVersion(model, version_id = version_id, time_until = time_until)
            version = version_data[3]
            version = version if version is not None else ''
            created = version_data[1].strftime('%m/%d/%Y')
            docpath = version_data[5]
            docpath = docpath if docpath is not None else ''
            user = version_data[2]
            desc = version_data[6]
            desc_formatted = []
            if desc is not None and len(desc) > 0:
                desc = desc.split('\n')
                LINE_WIDTH = 200
                for i, para in enumerate(desc):
                    if i > 0: 
                        desc_formatted.append('\n')
                    last_line = desc[i][-1]
                    while len(last_line) > LINE_WIDTH:
                        split_ix = string.rfind(last_line, ' ', end = LINE_WIDTH)
                        desc_formatted.append(last_line[:split_ix])
                        last_line = last_line[(split_ix + 1):]
                    desc_formatted.append(last_line)

            swmmbuild = version_data[4]
            tags = self.getModelTagsCSV(model)
            preamble = ['SWMMR Profile',
                        'Model exported ' + datetime.now().strftime('%m/%d/%Y'),
                        '',
                        'Model:       ' + model,
                        'SWMM Build:  ' + swmmbuild,
                        'Version:     ' + version,
                        'Created:     ' + created,
                        'User:        ' + user,
                        'Tags:        ' + tags,
                        'Doc:         ' + docpath,
                        'Description: ']
            preamble.extend(desc_formatted)
            inp_f.write(preamble)
        else:
            inp_f.write()

    def CME_GetFieldChanges(self, cme_id):
        return self.sp('Framework.spCME_GetFieldChanges', CME_ID = cme_id)

    def exportLastINPBackup(self, model, path):
        inp = self.sp('Framework.spExportLastINPBackup', model = model)

        with open(path, 'w') as f:
            f.write(inp.decode('string-escape'))

#    def importUnsupportedSections(self, model, unsupported):
#        if unsupported is not None:
#            self.sp('Framework.spImportUnsupportedSections', model = model, unsupported = unsupported)

#    def getModelDescription(self, model_name, version_id = None, time_until = None):
#        return self.sp('Framework.spGetModelDescription', ModelName = model_name, VersionID = version_id, TimeUntil = time_until)

#    def addModelTags(self, model, tags_csv):
#        self.sp('Framework.spAddModelTags', ModelName = model, TagsCSV = tags_csv)

#    def dropModelTags(self, model, tags_csv):
#        self.sp('Framework.spDropModelTags', ModelName = model, TagsCSV = tags_csv)

#    def selectModelVersionID(self, model_name, time_until = None):
#        return self.sp('Framework.spSelectModelVersionID', ModelName = model_name, TimeUntil=time_until)
#
#    def selectModelVersion(self, model_name, version_id = None, time_until = None):
#        return self.sp('Framework.spSelectModelVersion', ModelName = model_name, VersionID = version_id, TimeUntil = time_until)
#
#    def selectModelVersionTimeStamp(self, model_name, version_id = None, time_until = None):
#        return self.sp('Framework.spSelectModelVersionTimeStamp', ModelName = model_name, VersionID = version_id, TimeUntil = time_until)

